/****** Object:  Stored Procedure dbo.asi_CreateRFMStatsAuto Script Date: 9/28/2005 11:04:02 AM ******/
CREATE PROCEDURE [dbo].[asi_CreateRFMStatsAuto]
    @rfmKey UNIQUEIDENTIFIER,
    @numGroupsRecency INT,
    @numGroupsFrequency INT,
    @numGroupsMonetary INT
AS

DECLARE @now DATETIME

SET @now = GETDATE()

-- new code
select
ContactKey,
count(*) as CT,
sum (Amount) as AMT,
min (Amount) as LOW,
max (Amount) as HIGH,
min ([TransactionDate]) as FIRST,
max ([TransactionDate]) as LAST,
convert (datetime, '1/1/90') as HIGHCONT,
10000000.0000 as FIRSTAMT,
10000000.0000 as LASTAMT
into #tmpTransWork
from #tmpTrans
group by ContactKey

update #tmpTransWork
set HIGHCONT = #tmpTrans.TransactionDate from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.HIGH = #tmpTrans.Amount


update #tmpTransWork
set FIRSTAMT = #tmpTrans.Amount from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.FIRST = #tmpTrans.TransactionDate


update #tmpTransWork
set LASTAMT = #tmpTrans.Amount from #tmpTrans
where #tmpTransWork.ContactKey = #tmpTrans.ContactKey
and #tmpTransWork.LAST = #tmpTrans.TransactionDate


-- existing code, updated statement
-- Update the RFMMain Table, set initial values for all columns
UPDATE RFMMain
SET NumContacts = T1.CT,
TotalAmount = T1.AMT,
LowAmount = T1.LOW,
HighAmount = T1.HIGH,
FirstContact = T1.FIRST,
LastContact = T1.LAST,
HighContact = T1.HIGHCONT,
FirstContactAmount = T1.FIRSTAMT,
LastContactAmount = T1.LASTAMT,
NumDays = DATEDIFF(DAY, T1.LAST, @now),
Frequency = 0,
Recency = 0,
Monetary = 0,
Combined = 0,
Total = 0
FROM
(SELECT
ContactKey,
CT,
AMT,
LOW,
HIGH,
FIRST,
LAST,
HIGHCONT,
FIRSTAMT,
LASTAMT
FROM #tmpTransWork) AS T1
WHERE RFMMain.RFMKey = @rfmKey AND RFMMain.ContactKey = T1.ContactKey

--DELETE from RFMMain WHERE RFMMain.RFMKey = @rfmKey AND RFMMain.NumContacts = 0  

DECLARE @numGroups INT
DECLARE @group_size INT
DECLARE @total_count INT
DECLARE @n INT
DECLARE @sql VARCHAR(500)

if (@numGroupsRecency > 0)
BEGIN
    -- Do the Recency Rankings
    SET @numGroups = @numGroupsRecency

    -- Get the Total Count
    -- Only choose records that have numdays
    select @total_count = count(*) from RFMMain WHERE RFMMain.RFMKey = @rfmKey AND NumDays IS NOT NULL
    
    IF (@total_count > 0)
    BEGIN
        PRINT 'Recency Total Count = ' + str(@total_count) + ' Num Groups = ' + str(@numGroups)
        SET @group_size = ROUND(CAST(@total_count AS FLOAT) / @numGroups, 0)

        PRINT 'Recency Group Size = ' + str(@group_size)

        IF (@group_size = 0)
            SET @group_size = 1
            
        SET @n = 1

        WHILE (@n <= @numGroups)
        BEGIN
            -- Build the SQL dynamically. We have to do this in order to use
            -- the TOP clause with a variable
            IF (@n < @numGroups)
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Recency = ' + str(@n) +
                    ' FROM ( SELECT TOP ' + str(@group_size) + ' * FROM RFMMain ' +
                    ' WHERE NumDays IS NOT NULL AND Recency = 0' +
                    ' ORDER BY NumDays DESC) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            ELSE
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Recency = ' + str(@n) +
                    ' FROM ( SELECT * FROM RFMMain ' +
                    ' WHERE NumDays IS NOT NULL AND Recency = 0' +
                    ' ) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            
            PRINT 'Sql = ' + @sql
            -- Execute the SQL
            EXEC (@sql)
                        
            SET @n = @n+1
        END
    END
END

if (@numGroupsFrequency > 0)
BEGIN
    -- Do the Frequency Rankings
    SET @numGroups = @numGroupsFrequency

    -- Get the Total Count
    select @total_count = count(*) from RFMMain WHERE RFMMain.RFMKey = @rfmKey AND NumContacts IS NOT NULL AND NumContacts != 0
    
    IF (@total_count > 0)
    BEGIN
        PRINT 'Frequency Total Count = ' + str(@total_count) + ' Num Groups = ' + str(@numGroups)
        SET @group_size = ROUND(CAST(@total_count AS FLOAT) / @numGroups, 0)

        PRINT 'Frequency Group Size = ' + str(@group_size)
            
        SET @n = 1

        IF (@group_size = 0)
            SET @group_size = 1

        WHILE (@n <= @numGroups)
        BEGIN
            -- Build the SQL dynamically. We have to do this in order to use
            -- the TOP clause with a variable
            IF (@n < @numGroups)
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Frequency = ' + str(@n) +
                    ' FROM ( SELECT TOP ' + str(@group_size) + ' * FROM RFMMain ' +
                    ' WHERE NumContacts IS NOT NULL AND NumContacts != 0 AND Frequency = 0' +
                    ' ORDER BY NumContacts ASC) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            ELSE
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Frequency = ' + str(@n) +
                    ' FROM ( SELECT * FROM RFMMain ' +
                    ' WHERE NumContacts IS NOT NULL AND NumContacts != 0 AND Frequency = 0' +
                    ' ) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            
            -- Execute the SQL
            EXEC (@sql)
                        
            SET @n = @n+1
        END
    END
END

if (@numGroupsMonetary > 0)
BEGIN
    -- Do the Monetary Rankings
    SET @numGroups = @numGroupsMonetary

    -- Get the Total Count
    select @total_count = count(*) from RFMMain WHERE RFMMain.RFMKey = @rfmKey AND TotalAmount IS NOT NULL AND TotalAmount != 0

    IF (@total_count > 0)
    BEGIN
        PRINT 'Monetary Total Count = ' + str(@total_count) + ' Num Groups = ' + str(@numGroups)
        SET @group_size = ROUND(CAST(@total_count AS FLOAT) / @numGroups, 0)

        PRINT 'Monetary Group Size = ' + str(@group_size)
        IF (@group_size = 0)
            SET @group_size = 1
            
        SET @n = 1

        WHILE (@n <= @numGroups)
        BEGIN
            -- Build the SQL dynamically. We have to do this in order to use
            -- the TOP clause with a variable
            IF (@n < @numGroups)
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Monetary = ' + str(@n) +
                    ' FROM ( SELECT TOP ' + str(@group_size) + ' * FROM RFMMain ' +
                    ' WHERE TotalAmount IS NOT NULL AND TotalAmount != 0 AND Monetary = 0' +
                    ' ORDER BY TotalAmount ASC) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            ELSE
            BEGIN
                SELECT @sql = 'UPDATE RFMMain SET Monetary = ' + str(@n) +
                    ' FROM ( SELECT * FROM RFMMain ' +
                    ' WHERE TotalAmount IS NOT NULL AND TotalAmount != 0 AND Monetary = 0' +
                    ' ) AS T1' +
                    ' WHERE RFMMain.RFMKey = T1.RFMKey AND RFMMain.ContactKey = T1.ContactKey';
            END
            
            -- Execute the SQL
            EXEC (@sql)
                        
            SET @n = @n+1
        END
    END
END

-- Set the combined and total rankings    
update RFMMain
    set Combined = Recency*10000+Frequency*100+Monetary,
    Total = Recency+Frequency+Monetary
    where RFMMain.RFMKey = @rfmKey

drop table #tmpTrans

GO